CREATE DEFINER=`mhcb`@`%` PROCEDURE `GetProgressTitle_new`(IN ParmScheduleItemID int,IN ParmAssessmentID int,
IN ParmPatientID int)
BEGIN


set Visit_type = (SELECT Visit_type  FROM ScheduleItems  where
 Assessment_ID= ParmAssessmentID  and  ScheduleItems_ID= ParmScheduleItemID);
 
set progress_Count = (SELECT count(*) as TotalCount FROM PatientVisitDetails where
 Assessment_ID= ParmAssessmentID  and  ScheduleItems_ID= ParmScheduleItemID);
 
if (Visit_type = 'Skilled Nursing') then
 set Skill = 'SN';
else if (Visit_type = 'Physical Therapy') then
set Skill = 'PT';
else if (Visit_type = 'Occupational Therapy') then
set Skill = 'OT';
else if (Visit_type = 'Speech Therapy') then
set Skill = 'ST';
else if (Visit_type = 'Medical Social Worker') then
set Skill = 'MSW';
else if (Visit_type = 'Home Health Aide') then
set Skill = 'HHA';
else if (Visit_type = 'Personal Care Attendant I') then
set Skill = 'PCA I';
else if (Visit_type = 'Personal Care Attendant II') then
set Skill = 'PCA II';
else if (Visit_type = 'Personal Care Attendant III') then
set Skill = 'PCA III';
else if (Visit_type = 'Homemaker') then
set Skill = 'HMKR';
else if (Visit_type = 'Companion') then
set Skill = 'COMP';
else if (Visit_type = 'Hospice') then
set Skill = 'HHH';
else if (Visit_type = 'Volunteer') then
set Skill = 'VOL';
else if (Visit_type = 'Skilled Nursing Hospice') then
set Skill = 'HSN';
else if (Visit_type = 'Medical Social Work Hospice') then
set Skill = 'HMS';
else if (Visit_type = 'Bereavement Hospice') then
set Skill = 'BRV';
else if (Visit_type = 'Spiritual Hospice') then
set Skill = 'SPR';
else if (Visit_type = 'Evaluation') then
set Skill = 'SN';
else if (Visit_type = 'Chaplain') then
set Skill = 'SPR';
end if;

if(progress_Count =  0) then
 
 INSERT INTO PatientVisitDetails(Pathway_Details_ID,ScheduleItems_ID,Item_Number,Intervention_Code,Type,Wnd_Type,Description,Agency_ID,
															Patient_ID,Assessment_ID,Assessment_Tracker_ID,Record_Creation_Date)
VALUES  (select Pathway_Details_ID,ParmScheduleItemID,Item_Number,Intervention_Code,Type,Description,Description,Agency_ID,Patient_ID,Assessment_ID,
					Assessment_Tracker_ID,Now()  from Pathway_Details pd where Assessment_ID = ParmAssessmentID
														and Type like '%vital%'
														and Skill='' and Deleted is null and Resolved_Date is null);
                                                        
end if;

Select distinct head from 

 (select pd.Pathway_Details_Id,pvd.Progress_Details_Id,

replace(replace(pvd.Description,'[',''),']','') as Description
,pvd.Performed,pvd.Reading1,pvd.Severity,pvd.Notes,pd.Resolved_Date  
,pvd.Type,pd.Agency_id
,pd.skill
,
case when pd.skill like 'sn' then
case  when  pvd.Type like '%vital%' then 'vital'
when pvd.Type like '%lab%' then 'labwork' 
when pd.Care_Pathway IN (12, 14, 27, 40) then 'Cardio / Respiratory / Hematology'
when pd.Care_Pathway IN (4, 13, 21, 24, 26, 33, 42, 900, 500, 600, 700, 704, 800, 0) then 'ADL Management Homebound Status'
when pd.Care_Pathway IN (2, 3, 18, 22) then 'Neuro / Emotional / Behavioral'
when pd.Care_Pathway IN (9) then 'Endocrine'
when pd.Care_Pathway IN (11, 23, 35, 36, 37) then 'Nutrition'
when pd.Care_Pathway IN (10) then 'Gastrointestinal'
when pd.Care_Pathway IN (19) then 'Genitourinary'
when pd.Care_Pathway IN (25, 34, 35, 38, 39) then 'Integumentary'


else '' end 
when pd.skill like 'HSN' then
case  when  pvd.Type like '%vital%' then 'vital'
when  pvd.Type like '%lab%' then 'labwork' 
when pd.Care_Pathway IN (12, 14, 27, 40) then 'Cardio / Respiratory / Hematology'
when pd.Care_Pathway IN (4, 13, 21, 24, 26, 33, 42, 900, 500, 600, 700, 704, 800, 0) then 'ADL Management Homebound Status'
when pd.Care_Pathway IN (2, 3, 18, 22) then 'Neuro / Emotional / Behavioral'
when pd.Care_Pathway IN (9) then 'Endocrine'
when pd.Care_Pathway IN (11, 23, 35, 36, 37) then 'Nutrition'
when pd.Care_Pathway IN (10) then 'Gastrointestinal'
when pd.Care_Pathway IN (19) then 'Genitourinary'
when pd.Care_Pathway IN (25, 34, 35, 38, 39) then 'Integumentary'

end

when pd.skill like 'SPR' or pd.skill like 'PT'
or pd.skill like 'OT' or  pd.skill like 'ST'
or pd.skill like 'MSW' or  pd.skill like 'BRV'
or pd.skill like 'HHA' or  pd.skill like 'PCA I'
or pd.skill like 'PCA II' or  pd.skill like 'PCA III'
or pd.skill like 'HMKR' or  pd.skill like 'COMP'
or  pd.skill like 'VOL'  or  pd.skill like 'HMS'
then
case  when  pvd.Type like '%vital%' then 'vital'
when  pvd.Type like '%lab%' then 'labwork' 
else 'ADL Management Homebound Status'
end 
end as Head
,pd.Item_Number,pd.Intervention_Code,pd.Care_Pathway
,pvd.Wnd_Len_1 
,pvd.Wnd_Len_2 
,pvd.Wnd_Wid_1
,pvd.Wnd_Wid_2 
,pvd.Wnd_Dep_1 
,pvd.Wnd_Dep_2 
,pvd.Wnd_Surr_Tiss 
,pvd.Wnd_Chara
,pvd.Wnd_Type
,pvd.Wnd_Location
,pvd.Wnd_Stage
,pvd.Wnd_Status
from PatientVisitDetails pvd  
Inner Join Pathway_Details pd on pvd.Pathway_Details_Id=pd.Pathway_Details_Id
where

pvd.ScheduleItems_ID=ParmScheduleItemID and pvd.patient_id=ParmPatientID
and pd.Resolved_Date is null
) as temp
Where head is not null
; 


END